<%@ page language="java" contentType="text/html; charset=UTF-8" %>
<%@page import="weaver.hrm.attendance.domain.HrmLeaveTypeColor"%>
<jsp:useBean id="colorManager" class="weaver.hrm.attendance.manager.HrmLeaveTypeColorManager" scope="page" /><!-- 日期时间类 -->

<%@ include file="/hrm/header.jsp" %>
<jsp:useBean id="rs1" class="weaver.conn.RecordSet" scope="page" />
<jsp:useBean id="rs2" class="weaver.conn.RecordSet" scope="page" />

<%
	Calendar today = Calendar.getInstance (); //日期时间类
	String currentDate = Util.add0(today.get(Calendar.YEAR), 4) + "-"
					   + Util.add0(today.get(Calendar.MONTH) + 1, 2) + "-"
					   + Util.add0(today.get(Calendar.DAY_OF_MONTH), 2);//获取当前日期

	String fromDate = strUtil.vString(request.getParameter("fromDate"));//开始时间
	String toDate = strUtil.vString(request.getParameter("toDate"));//结束时间
	
	String XMNo = Util.null2String(request.getParameter("XMNo"));//项目编号
	
	String fileName = fromDate+" "+SystemEnv.getHtmlLabelName(15322,user.getLanguage())+" "+toDate+" 的项目部报表  餐厅编号="+XMNo;//标题
 
%>
<!--<table  border=0 width="100%" >
	<tbody>
		<tr>
			<td align="center" ><font size=4><b><%=fileName%></b></font></td><!-- 标题 
		</tr>
		<tr>
			<td align="right" ></td>
		</tr>
	</tbody>
</table> -->

<table  border=1  bordercolor=black style="border-collapse:collapse;" width="100%" >
	<tbody>
		<tr>
			<td align="center" colspan="13">项目基本信息</td>
			<td align="center" colspan="3">项目费用</td>
			<td align="center">项目变更</td>
			<td align="center">项目收款情况</td>
			<td align="center" colspan="4">项目付款情况</td>
			<td align="center" colspan="9">手续付款情况</td>
			<!--<td align="center">其他</td>-->
		</tr>
		<tr>
			<td align="center" width="3.3%">序号</td>
			<td align="center" width="3.3%">项目编号</td>
			<td align="center" width="3.3%">项目名称</td>
			<td align="center" width="3.3%">项目类型</td>
			<!--<td align="center">项目种类</td>-->
			<td align="center" width="3.3%">甲方联系人</td>
			<td align="center" width="3.3%">甲方所属公司</td>
			<td align="center" width="3.3%">项目地址</td>
			<td align="center" width="3.3%">公司项目经理</td>
			<td align="center" width="3.3%">设计公司名称</td>
			<td align="center" width="3.3%">项目设计风格</td>
			<!--<td align="center">合同签署情况</td>-->
			<td align="center" width="3.3%">保险起止时间</td>
			<td align="center" width="3.3%">竣工日期</td>
			<td align="center" width="3.3%">质保日期</td>
			<td align="center" width="3.3%">工程造价</td>
			<td align="center" width="3.3%">税率</td>
			<td align="center" width="3.3%">代购费用</td>
			<td align="center" width="3.3%">变更1</td>
			<!--<td align="center">第一期30%</td>
			<td align="center">第二期45%</td>
			<td align="center">第三期20%</td>
			<td align="center">第四期5%</td>-->
			<td align="center" width="3.3%">代购费用</td>
			<td align="center" width="3.3%">项目承包方式</td>
			<td align="center" width="3.3%">项目班组</td>
			<td align="center" width="3.3%">应付款金额</td>
			<td align="center" width="3.3%">已付款金额</td>
			<td align="center" width="3.3%">消防分包单位</td>
			<td align="center" width="3.3%">应付款金额</td>
			<td align="center" width="3.3%">已付款金额</td>
			<td align="center" width="3.3%">环保分包单位</td>
			<td align="center" width="3.3%">应付款金额</td>
			<td align="center" width="3.3%">已付款金额</td>
			<td align="center" width="3.3%">招牌分包单位</td>
			<td align="center" width="3.3%">应付款金额</td>
			<td align="center" width="3.3%">已付款金额</td>
			<!--<td align="center">备注</td>-->
		</tr>
		<%
			String sql = "select a.xmbh,a.xmmc,c.xmxz,a.xmjflxr,a.xmjgs1,a.xmdz,d.xmjl,e.sjgs,e.zxfg,e.bxkssj,e.bxjssj,e.bszzjg,e.sl,e.cbfs,f.htje,g.jgrq,h.bgbsjg,i.zflxzje,j.zfqkzje,k.yfkje";
			sql += " from formtable_main_42 a left join (select c.selectvalue,c.selectname xmxz from workflow_SelectItem c where c.fieldid='7897') c on a.xmxz=c.selectvalue";
			sql += " left join (select d.xmbh,e.lastname xmjl from formtable_main_54 d,HrmResource e where d.xmjl=e.id) d on a.xmbh=d.xmbh";
			sql += " left join (select e.xmbh,f.sjgs,g.zxfg,e.bxkssj,e.bxjssj,e.bszzjg,h.sl,i.cbfs from formtable_main_48 e";
			sql += " left join (select c.selectvalue,c.selectname sjgs from workflow_SelectItem c where c.fieldid='7164') f on e.sjgs=f.selectvalue";
			sql += " left join (select c.selectvalue,c.selectname zxfg from workflow_SelectItem c where c.fieldid='8078') g on e.zxfg=g.selectvalue";
			sql += " left join (select c.selectvalue,c.selectname sl from workflow_SelectItem c where c.fieldid='8365') h on e.zxfg=h.selectvalue";
			sql += " left join (select c.selectvalue,c.selectname cbfs from workflow_SelectItem c where c.fieldid='8086') i on e.zxfg=i.selectvalue) e on a.xmbh=e.xmbh";
			sql += " left join (select f.xmbh,sum(f.htje) htje  from formtable_main_70 f group by f.xmbh) f on a.xmbh=f.xmbh";
			sql += " left join formtable_main_59 g on a.xmbh=g.xmbh";
			sql += " left join (select h.xmbh,sum(h.bgbsjg) bgbsjg from formtable_main_57 h group by h.xmbh) h on a.xmbh=h.xmbh";
			sql += " left join (select xmbh,sum(zje) zflxzje from formtable_main_34 where zflx='0' group by xmbh) i on a.xmbh=i.xmbh";
			sql += " left join (select xmbh,sum(zje) zfqkzje from formtable_main_34 where zfqk='1' group by xmbh) j on a.xmbh=j.xmbh";
			sql += " left join (select xmbh,sum(zje) yfkje from formtable_main_92 group by xmbh) k on a.xmbh =k.xmbh";
			
			if(!fromDate.equals("") && !toDate.equals("")){
				sql += " where a.sqrq between '"+fromDate+"' and '"+toDate+"'";
			}else if(!fromDate.equals("")){
				sql += " where a.sqrq='"+fromDate+"'";
			}else if(!toDate.equals("")){
				sql += " where a.sqrq='"+toDate+"'";
			}
			
			if(!fromDate.equals("") || !toDate.equals("")){
				if(!XMNo.equals("")){
					sql += " and a.xmbh like '%"+XMNo+"%'";
				}
			}else{
				if(!XMNo.equals("")){
					sql += " where a.xmbh like '%"+XMNo+"%'";
				}
			}
			
			sql += " order by a.xmbh";
			rs.executeSql(sql);
			int xh = 0;
			while(rs.next()){
				out.print("<tr>");
				xh = xh+1;
				String xmbh = strUtil.vString(rs.getString("xmbh"));
				out.print("<td align='center'>"+xh+"</td>");
				out.print("<td align='center'>"+xmbh+"</td>");//项目编号
				out.print("<td align='center'>"+rs.getString("xmmc")+"</td>");//项目名称
				out.print("<td align='center'></td>");//项目类型
				//out.print("<td align='center'>"+rs.getString("xmxz")+"</td>");//项目种类
				out.print("<td align='center'>"+rs.getString("xmjflxr")+"</td>");//甲方联系人
				out.print("<td align='center'>"+rs.getString("xmjgs1")+"</td>");//甲方所属公司
				out.print("<td align='center'>"+rs.getString("xmdz")+"</td>");//项目地址
				out.print("<td align='center'>"+rs.getString("xmjl")+"</td>");//公司项目经理
				out.print("<td align='center'>"+rs.getString("sjgs")+"</td>");//设计公司名称
				out.print("<td align='center'>"+rs.getString("zxfg")+"</td>");//项目设计风格
				//out.print("<td align='center'></td>");//合同签署情况
				
				String bxkssj = rs.getString("bxkssj");//保险开始时间
				String bxjssj = rs.getString("bxjssj");//保险结束时间
				if(!bxkssj.equals("") && !bxjssj.equals("")){
					out.print("<td align='center'>"+bxkssj+"至"+bxjssj+"</td>");//保险起止时间
				}else if(!bxkssj.equals("")){
					out.print("<td align='center'>"+bxkssj+"</td>");//保险起止时间
				}else if(!bxjssj.equals("")){
					out.print("<td align='center'>"+bxjssj+"</td>");//保险起止时间
				}else{
					out.print("<td align='center'></td>");//保险起止时间
				}
				
				out.print("<td align='center'>"+rs.getString("jgrq")+"</td>");//竣工日期
				out.print("<td align='center'></td>");//质保日期
				out.print("<td align='center'>"+rs.getString("bszzjg")+"</td>");//工程造价
				out.print("<td align='center'>"+rs.getString("sl")+"</td>");//税率
				out.print("<td align='center'>"+rs.getString("zflxzje")+"</td>");//代购费用
				out.print("<td align='center'>"+rs.getString("bgbsjg")+"</td>");//变更1
				//out.print("<td align='center'></td>");//第一期30%
				//out.print("<td align='center'></td>");//第二期45%
				//out.print("<td align='center'></td>");//第三期20%
				//out.print("<td align='center'></td>");//第四期5%
				out.print("<td align='center'>"+rs.getString("zfqkzje")+"</td>");//代购费用
				out.print("<td align='center'>"+rs.getString("cbfs")+"</td>");//项目承包方式
				
				String sgbz = "";
				String bzxm = "";
				String kgbgSql = "select bzxm from formtable_main_48 a,formtable_main_48_dt1 b where a.id=b.mainid and a.xmbh='"+xmbh+"'";
				rs1.executeSql(kgbgSql);
				while(rs1.next()){
					 String a = strUtil.vString(rs1.getString("bzxm"));
					 if(!bzxm.equals(a)){
						bzxm = a;
						String hrmSql = "select lastname from HrmResource where id='"+bzxm+"'";
						rs2.executeSql(hrmSql);
						while(rs2.next()){
							if(!sgbz.equals("")){
								sgbz += ","+strUtil.vString(rs2.getString("lastname"));
							}else{
								sgbz += strUtil.vString(rs2.getString("lastname"));
							}
						}
					 }
				}
				out.print("<td align='center'>"+sgbz+"</td>");//项目班组
				
				out.print("<td align='center'>"+rs.getString("htje")+"</td>");//应付款金额
				out.print("<td align='center'>"+rs.getString("yfkje")+"</td>");//已付款金额
				
				String xfhtyf = "";//分包单位名称-消防
				String xfhtje = "";//合同价格-消防
				String hbhtyf = "";//分包单位名称-环保
				String hbhtje = "";//合同价格-环保
				String zphtyf = "";//分包单位名称-招牌
				String zphtje = "";//合同价格-招牌
				
				String htSql = "select a.xmbh,a.fbqk,b.gys,a.htje from formtable_main_70 a left join uf_gys b on a.htyf=b.id where a.xmbh='"+xmbh+"'";
				rs1.executeSql(htSql);
				while(rs1.next()){
					String fbqk = strUtil.vString(rs1.getString("fbqk"));//分包情况
					if(fbqk.equals("0")){//消防
						xfhtyf = strUtil.vString(rs1.getString("gys"));
						xfhtje = strUtil.vString(rs1.getString("htje"));
					}else if(fbqk.equals("1")){//环保
						hbhtyf = strUtil.vString(rs1.getString("gys"));
						hbhtje = strUtil.vString(rs1.getString("htje"));
					}else if(fbqk.equals("2")){//招牌
						zphtyf = strUtil.vString(rs1.getString("htyf"));
						zphtje = strUtil.vString(rs1.getString("htje"));
					}
				}
				String xfzje = "";//总金额-消防
				String hbzje = "";//总金额-环保
				String zpzje = "";//总金额-招牌
				String qksql = "select gysss,zje from formtable_main_34 where xmbh='"+xmbh+"'";
				rs1.executeSql(qksql);
				while(rs1.next()){
					String gysss = strUtil.vString(rs1.getString("gysss"));//分包情况
					if(gysss.equals("0")){//消防
						xfzje = strUtil.vString(rs1.getString("zje"));
					}else if(gysss.equals("1")){//环保
						hbzje = strUtil.vString(rs1.getString("zje"));
					}else if(gysss.equals("2")){//招牌
						zpzje = strUtil.vString(rs1.getString("zje"));
					}
				}
				
				out.print("<td align='center'>"+xfhtyf+"</td>");//消防分包单位
				out.print("<td align='center'>"+xfhtje+"</td>");//应付款金额
				out.print("<td align='center'>"+xfzje+"</td>");//已付款金额
				out.print("<td align='center'>"+hbhtyf+"</td>");//环保分包单位
				out.print("<td align='center'>"+hbhtje+"</td>");//应付款金额
				out.print("<td align='center'>"+hbzje+"</td>");//已付款金额
				out.print("<td align='center'>"+zphtyf+"</td>");//招牌分包单位
				out.print("<td align='center'>"+zphtje+"</td>");//应付款金额
				out.print("<td align='center'>"+zpzje+"</td>");//已付款金额
				//out.print("<td align='center'>"+rs.getString("")+"</td>");//备注
				out.print("</tr>");
			}
		%>
	</tbody>
</table>
<table border=0 width="100%">
	<tbody>
		<tr>
			<td align="right" ><%=SystemEnv.getHtmlLabelName(20087,user.getLanguage())+"："+currentDate%></td>
		</tr>
	</tbody>
</table>
